package com.qingcheng.dao;

import com.qingcheng.pojo.order.CategoryReport;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import tk.mybatis.mapper.common.Mapper;

import java.time.LocalDate;
import java.util.List;
import java.util.Map;

/**
 * Welcome to the most Amazing IntelliJ IIIDEA.
 * Creat0r: ^_^ fffantianxing ^_^
 * Date: 2019-06-27
 * Time: 20:33
 */

public interface CategoryReportMapper extends Mapper<CategoryReport> {

    @Select("SELECT\n" +
            "\toi.`category_id1`, oi.`category_id2`, oi.`category_id3`, DATE_FORMAT(pay_time,'%Y-%m-%d') count_date,SUM(oi.`num`) num,SUM(oi.`pay_money`) money\n" +
            "FROM\n" +
            "\ttb_order o,\n" +
            "\ttb_order_item oi\n" +
            "WHERE\n" +
            "\to.`id`=oi.`order_id`\n" +
            "AND\n" +
            "\to.`is_delete`='0'\n" +
            "AND\n" +
            "\to.`pay_status`='1'\n" +
            "AND\n" +
            "\tDATE_FORMAT(pay_time,'%Y-%m-%d')=#{date}\n" +
            "GROUP BY\n" +
            "\toi.`category_id3`")
    public List<CategoryReport> categotyReport(LocalDate date);

    @Select("SELECT\n" +
            "\tcategory_id1 category_id1, v.`name` categoryName, SUM(num) num, SUM(money) money\n" +
            "FROM\n" +
            "\ttb_category_report c, v_category1 v\n" +
            "WHERE\n" +
            "\tcount_date >=#{date1}\n" +
            "AND\n" +
            "\tcount_date <=#{date2}\n" +
            "AND\n" +
            "\tc.`category_id1`=v.`id`\n" +
            "GROUP BY\n" +
            "\tcategory_id1")
    public List<Map> category1(@Param("date1") String date1, @Param("date2") String date2);
}
